from public import SQL_Conn

conn = SQL_Conn()


# region数据库基础操作 find、insert_one，insert_many，update_one ，update_many

# 查找数据
def find(table_name, lookup_col_list=None, data=None):
    """
    查询数据
    :param table_name:
    :param col_list:
    :param lookup_col_list:
    :param data:
    :return:
    """
    sql_str = f"select * from {table_name}  "
    if lookup_col_list and data:
        lookup_str = get_lookup_str(lookup_col_list)
        sql_str += lookup_str
    return find_data(sql_str, data)


def check_data_exit(con_str, index_list, data):
    para = get_sub_col_data(data, index_list)
    return find_data(con_str, para)


def get_check_data_str(table_name, find_col_list):
    conn_str = f"select id from {table_name}"
    lookup_col_str = get_lookup_str(find_col_list)
    return conn_str + lookup_col_str


def get_col_index(col_list: list, sub_col_list: list):
    index_list = []
    for col in sub_col_list:
        index = col_list.index(col)
        index_list.append(index)
    return index_list


def get_sub_col_data(data, index_list):
    new_data = []
    for index in index_list:
        new_data.append(data[index])
    return new_data


def check_data_list(table_name, data_list: list, find_col_list):
    insert_list = []
    update_list = []

    find_col_index = get_col_index()

    for data in data_list:
        result = find(table_name, find_col_list, data)
        if result:
            update_list.append(data)
        else:
            insert_list.append(data)
    return {"insert": insert_list, "update": update_list}


def modify_table_many(table, col_name_list, data_list, check_col_list):
    find_value_index = col_name_list.index(check_col_list)
    data_dic = check_data_list(table, check_col_list, data_list, find_value_index)
    if data_dic["insert"]:
        insert_many(table, col_name_list, data_dic["insert"])
    if data_dic["update"]:
        update_many(table, col_name_list, data_dic["insert"])


def insert_one(table, col_name_list, data):
    sql_str = insert_str(table, col_name_list)
    tread_one(sql_str, data)


def insert_many(table, col_name_list, data):
    sql_str = insert_str(table, col_name_list)
    tread_many(sql_str, data)


def update_one(table, col_name_list, data):
    sql_str = update_str(table, col_name_list)
    tread_one(sql_str, data)


# endregion

def update_many(table, col_name_list, look_up_list, data_list):
    sql_str = update_str(table, col_name_list, look_up_list)
    tread_many(sql_str, data_list)


# region 生产各种SQL字符串 insert_str，get_lookup_str，update_str
def insert_str(table_name, col_name_list: list):
    col_str = ",".join(col_name_list)
    values_para_str = "?," * (len(col_name_list) - 1) + "?"

    insert_sql_str = f" insert into {table_name} ({col_str}) values ({values_para_str})"
    return insert_sql_str


def get_lookup_str(look_up_col_list):
    lookup_str = ""
    if look_up_col_list:
        lookup_str = "where "
        if len(look_up_col_list) == 1:
            lookup_str += f" {look_up_col_list[0]}= ? "
        else:
            lookup_str = " = ? and ".join(look_up_col_list) + "=?"
    return lookup_str


def update_str(table_name, col_name_list: list, look_up_col_list):
    new_col_list = remove_col(col_name_list, look_up_col_list)
    col_name_list.pop()
    "=?,".join(col_name_list)
    update_sql_str = f"update {table_name} set " + "=?,".join(new_col_list) + "=?"
    lookup_str = get_lookup_str(look_up_col_list)
    update_sql_str += lookup_str
    return update_sql_str


def remove_col(col_name_list, remove_col_list: list):
    new_col_list = []
    for col in col_name_list:
        if col in remove_col_list:
            remove_col_list.remove(col)
        else:
            new_col_list.append(col)
    return new_col_list


def list_first_to_end(data_list: list):
    new_data_list = data_list.copy()
    if new_data_list and len(new_data_list) > 2:
        first_value = new_data_list[0]
        new_data_list.pop(0)
        new_data_list.append(first_value)
    return new_data_list


def index_to_end(data_list, index):
    new_data_list = data_list.copy()
    value = data_list[index]
    new_data_list.pop(index)
    new_data_list.append(value)
    return new_data_list


# endregion


# region 基础SQL 语句: find_data,tread_one,tread_many

def tread_one(sql_str, data=None):
    cur = cmd_sql(sql_str, data)
    conn.commit()
    cur.close()


def tread_many(sql_str, para):
    c = conn.cursor()
    c.executemany(sql_str, para)
    conn.commit()
    c.close()


def find_data(sql_str: str, para=None):
    """
    查找数据
    Args:
        sql_str: sql语句
        data: 查询的数据
    Returns:查询到的数据
    :param sql_str:
    :param para:
    """

    c = cmd_sql(sql_str, para)
    data = c.fetchall()
    c.close()
    return data


def cmd_sql(sql_str: str, para=None):
    c = conn.cursor()
    if para:
        c.execute(sql_str, para)
        return c
    else:
        c.execute(sql_str)
        return c
# endregion
